package com.study.samsung.Dao;

import cn.hutool.core.util.StrUtil;
import cn.hutool.db.sql.Order;
import com.study.samsung.Entity.*;
import com.study.samsung.Utils.OrderPager;
import com.study.samsung.Utils.RowMapper;
import com.study.samsung.Utils.SQLHelper;
import com.sun.org.apache.xpath.internal.operations.Or;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrdersDao {
    SQLHelper helper = new SQLHelper();

    public List<Orders> selectOrders(Integer uid){
        String sql = "select * from orders where uid = ? order by did desc";
        return helper.query(sql,new OrdersRowMapper(),uid);
    }

    public List<Orders> selectOrdersaid(Integer did){
        String sql = "select * from orders where did = ?";
        return helper.query(sql,new OrdersRowMapper(),did);
    }

    public Integer selectCount() {
        String sql = "SELECT COUNT(*) FROM  order_details";
        return helper.one(sql, new RowMapper<Integer>() {
            @Override
            public Integer map(ResultSet rs) throws SQLException {
                return rs.getInt(1);
            }
        });
    }

    //    带条件分页查询
    public OrderPager selectByPager(Integer pageNo,Integer pageSize,String[] arr){
        StringBuilder sql = new StringBuilder("");
        List<Object> param = new ArrayList<>();

        if (arr.length>0){
            if (!StrUtil.isEmpty(arr[0])){
                sql.append(" and a.dstatus=?");
                param.add(arr[0]);
            }
            if (!StrUtil.isEmpty(arr[1])){
                sql.append(" and a.pay=?");
                param.add(arr[1]);
            }
            if (!StrUtil.isEmpty(arr[2])&&!StrUtil.isEmpty(arr[3])){
                sql.append("and (a.dtime>=? and a.dtime<=?)");
                param.add(arr[2]);
                param.add(arr[3]);
            }else if (!StrUtil.isEmpty(arr[2])&&StrUtil.isEmpty(arr[3])){
                sql.append("and a.dtime>=?");
                param.add(arr[2]);
            }else if (StrUtil.isEmpty(arr[2])&&!StrUtil.isEmpty(arr[3])){
                sql.append("and a.dtime<=?");
                param.add(arr[3]);
            }
            if (!StrUtil.isEmpty(arr[4])){
                sql.append("and a.dnumber=?");
                param.add(arr[4]);
            }
        }
        //查询总条数
        Integer total = helper.one("SELECT count(*) FROM orders a LEFT JOIN order_details b ON a.did=b.did " +
                "LEFT JOIN USER c ON a.uid=c.uid " +
                "LEFT JOIN address d ON a.aid=d.aid " +
                "LEFT JOIN detail e ON b.sxid=e.sxid " +
                "LEFT JOIN commodity f ON e.sid=f.sid " +
                "LEFT JOIN commoditymemory g ON e.nid = g.nid " +
                "LEFT JOIN commoditycolor h ON e.yid=h.yid WHERE 1=1 " +sql.toString(), new RowMapper<Integer>() {
            @Override
            public Integer map(ResultSet rs) throws SQLException {
                return rs.getInt(1);
            }
        },param.toArray());

        sql.append("order by a.did desc limit ?,?");
        param.add((pageNo-1)*pageSize);
        param.add(pageSize);
        List<Orders> list = helper.query("SELECT * FROM orders a LEFT JOIN order_details b ON a.did=b.did " +
                "LEFT JOIN USER c ON a.uid=c.uid " +
                "LEFT JOIN address d ON a.aid=d.aid " +
                "LEFT JOIN detail e ON b.sxid=e.sxid " +
                "LEFT JOIN commodity f ON e.sid=f.sid " +
                "LEFT JOIN commoditymemory g ON e.nid = g.nid " +
                "LEFT JOIN commoditycolor h ON e.yid=h.yid WHERE 1=1 " + sql.toString(), new RowMapper<Orders>() {
            @Override
            public Orders map(ResultSet rs) throws SQLException {
                return new Orders(rs.getInt("did"),
                        rs.getString("dnumber"),
                        rs.getString("dtime"),
                        rs.getString("dstatus"),
                        rs.getString("pay"),
                        rs.getBigDecimal("dtotal"),
                        rs.getInt("uid"),
                        rs.getBoolean("isdel"),
                        rs.getInt("aid"),
                        new OrderDetails(rs.getInt("dxid"),
                                rs.getBigDecimal("dxprice"),
                                rs.getInt("gnumber"),
                                rs.getInt("sxid"),
                                rs.getInt("did"),
                                new Detail(rs.getInt("sxid"),
                                        rs.getBigDecimal("xprice"),
                                        rs.getInt("yid"),
                                        rs.getInt("nid"),
                                        rs.getInt("sid"),
                                        new Commoditycolor(rs.getInt("yid"),
                                                rs.getString("yname")),
                                        new Commoditymemory(rs.getInt("nid"),
                                                rs.getString("nname")),
                                        new Commodity(rs.getInt("sid"),
                                                rs.getString("sname"),
                                                rs.getString("url"),
                                                rs.getBigDecimal("sprice"),
                                                rs.getBoolean("isdefault"),
                                                rs.getInt("lid")))),
                        new Address(rs.getInt("aid"),
                                rs.getString("addressname"),
                                rs.getString("sheng"),
                                rs.getString("shi"),
                                rs.getString("qu"),
                                rs.getString("aname"),
                                rs.getString("aphone"),
                                rs.getInt("uid"),
                                rs.getInt("isdefault")),
                        new User(rs.getInt("uid"),
                                rs.getString("nickname"),
                                rs.getString("uname"),
                                rs.getString("sex"),
                                rs.getString("uphone"),
                                rs.getDate("birth"),
                                rs.getString("upicture"),
                                rs.getString("account"),
                                rs.getString("upwd"),
                                rs.getBoolean("isdel"))
                );
            }
        },param.toArray());
        OrderPager pager = new OrderPager(pageNo,pageSize);
        pager.setTotal(total);
        pager.setData(list);
        return pager;
    }




    //
    public List<Orders> selectOrderShade(Integer did){
        String sql = "SELECT * FROM orders a LEFT JOIN order_details b ON a.did=b.did " +
                "LEFT JOIN detail c ON b.sxid=c.sxid " +
                "LEFT JOIN commodity d ON c.sid = d.sid " +
                "LEFT JOIN commoditymemory e ON c.nid=e.nid " +
                "LEFT JOIN commoditycolor f ON c.yid=f.yid " +
                "LEFT JOIN USER g ON a.uid = g.uid " +
                "LEFT JOIN address h ON a.aid = h.aid where a.did=?";
        return helper.query(sql, new RowMapper<Orders>() {
            @Override
            public Orders map(ResultSet rs) throws SQLException {
                return new Orders(rs.getInt("did"),
                        rs.getString("dnumber"),
                        rs.getString("dtime"),
                        rs.getString("dstatus"),
                        rs.getString("pay"),
                        rs.getBigDecimal("dtotal"),
                        rs.getInt("uid"),
                        rs.getBoolean("isdel"),
                        rs.getInt("aid"),
                        new OrderDetails(rs.getInt("dxid"),
                                rs.getBigDecimal("dxprice"),
                                rs.getInt("gnumber"),
                                rs.getInt("sxid"),
                                rs.getInt("did"),
                                new Detail(rs.getInt("sxid"),
                                        rs.getBigDecimal("xprice"),
                                        rs.getInt("yid"),
                                        rs.getInt("nid"),
                                        rs.getInt("sid"),
                                        new Commoditycolor(rs.getInt("yid"),
                                                rs.getString("yname")),
                                        new Commoditymemory(rs.getInt("nid"),
                                                rs.getString("nname")),
                                        new Commodity(rs.getInt("sid"),
                                                rs.getString("sname"),
                                                rs.getString("url"),
                                                rs.getBigDecimal("sprice"),
                                                rs.getBoolean("isdefault"),
                                                rs.getInt("lid")))),
                        new Address(rs.getInt("aid"),
                                rs.getString("addressname"),
                                rs.getString("sheng"),
                                rs.getString("shi"),
                                rs.getString("qu"),
                                rs.getString("aname"),
                                rs.getString("aphone"),
                                rs.getInt("uid"),
                                rs.getInt("isdefault")),
                        new User(rs.getInt("uid"),
                                rs.getString("nickname"),
                                rs.getString("uname"),
                                rs.getString("sex"),
                                rs.getString("uphone"),
                                rs.getDate("birth"),
                                rs.getString("upicture"),
                                rs.getString("account"),
                                rs.getString("upwd"),
                                rs.getBoolean("isdel"))
                );
            }
        },did);
    }

    public Integer updateStatus(Integer did){
        String sql = "UPDATE orders SET dstatus='待收货' WHERE did=?";
        return helper.update(sql,did);
    }



    public List<Orders> selectAll(Integer uid){
        String sql = "select * from orders a left join order_details od on a.did = od.did " +
                "left outer join detail d on d.sxid = od.sxid " +
                "left join commoditycolor c on c.yid = d.yid " +
                "left join commoditymemory c2 on c2.nid = d.nid " +
                "left join commodity c3 on c3.sid = d.sid where uid = ?";
        return helper.query(sql,new OrdersRowMapper(){
            @Override
            public Orders map(ResultSet rs) throws SQLException {
                return new Orders(rs.getInt("did"),
                        rs.getString("dnumber"),
                        rs.getString("dtime"),
                        rs.getString("dstatus"),
                        rs.getString("pay"),
                        rs.getBigDecimal("dtotal"),
                        rs.getInt("uid"),
                        rs.getBoolean("isdel"),
                        rs.getInt("aid"),
                        new OrderDetails(rs.getInt("dxid"),
                                rs.getBigDecimal("dxprice"),
                                rs.getInt("gnumber"),
                                rs.getInt("sxid"),
                                rs.getInt("did"),
                                new Detail(rs.getInt("sxid"),
                                        rs.getBigDecimal("xprice"),
                                        rs.getInt("yid"),
                                        rs.getInt("nid"),
                                        rs.getInt("sid"),
                                        new Commoditycolor(rs.getInt("yid"),
                                                rs.getString("yname")),
                                        new Commoditymemory(rs.getInt("nid"),
                                                rs.getString("nname")),
                                        new Commodity(rs.getInt("sid"),
                                                rs.getString("sname"),
                                                rs.getString("url"),
                                                rs.getBigDecimal("sprice"),
                                                rs.getBoolean("isdefault"),
                                                rs.getInt("lid"))
                                ))
                );
            }
        },uid);
    }

    public Integer ordersinsert(String dxnumber, String dtime,String dstatus,String pay,Double dtotal,Integer uid,Integer aid){
        String sql = "insert into orders values(?,?,?,?,?,?,?,?,?)";
        return helper.update(sql,null,dxnumber,
                dtime,dstatus,pay,dtotal,uid,true,aid);
    }

    public Integer ordersdel(Integer did){
        String sql = "update orders set isdel = 0 where did=?";
        return helper.update(sql,did);
    }

    public Integer selectId(){
        String sql = "SELECT max(did) from orders";
        System.out.println("我在查最后一个订单id");
        return helper.one(sql,rs -> {
            return rs.getInt(1);
        });
    }

    public Integer cancelorder(Integer did){
        String sql = "update orders set dstatus=? where did=?";
        return helper.update(sql,"已取消",did);
    }

    class OrdersRowMapper implements RowMapper<Orders>{

        @Override
        public Orders map(ResultSet rs) throws SQLException {
            return new Orders(rs.getInt("did"),
                    rs.getString("dnumber"),
                    rs.getString("dtime"),
                    rs.getString("dstatus"),
                    rs.getString("pay"),
                    rs.getBigDecimal("dtotal"),
                    rs.getInt("uid"),
                    rs.getBoolean("isdel"),
                    rs.getInt("aid"));
        }
    }
}
